package chabernac.android.wordbattle.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import chabernac.android.wordbattle.model.RandomLetterHelper;

public class SQLiteStringDB extends SQLiteOpenHelper implements iStringDatabase{
  private static final int DATABASE_VERSION = 1;

  private Map<String, int[]> myLetterCounter = new HashMap<String, int[]>();
  private final Map<String, List<String>> myPrefixes = new HashMap<String, List<String>>();
  private final Map<String, List<String>> myWords = new HashMap<String, List<String>>();

  public SQLiteStringDB(Context context) {
    super(context, "WordBattleDB", null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL("Create table Language (LanguageCD CHARACTER(2) primary key)");
    db.execSQL("Create table Words (Word TEXT , LanguageCD CHARACTER(2), Length TINYINT, primary key(LanguageCD, Word))");
    //    db.execSQL("Constraint name WordsPK primary key(Words.LanguageCD, words.Word)");
    db.execSQL("Create index WordsIndex on Words (LanguageCD,Word)");
    db.execSQL("Create table Letters(Letter CHARACTER(1), LanguageCD CHARACTER(2), Occurences INT, primary key(LanguageCD, Letter))");
  }



  public void insertWord(String aLanguage, String aWord){
    aLanguage = aLanguage.toUpperCase();
    if(!myLetterCounter.containsKey( aLanguage )){
      myLetterCounter.put(aLanguage, new int[26]);
    }

    int[] theLetterCounter = myLetterCounter.get(aLanguage);

    ContentValues theValues = new ContentValues();
    aWord = aWord.toUpperCase();
    for(char theLetter : aWord.toCharArray()){
      if(theLetter >= 'A' && theLetter <='Z'){
        theLetterCounter[theLetter - 'A']++;
      }
    }
    //          Log.d(DatabaseHelper.class.getName(), "Inserting " + theWord);
    theValues.put("LanguageCD", aLanguage);
    theValues.put("Word", aWord);
    theValues.put("Length", aWord.length());
    getWritableDatabase().insert("Words", null, theValues);

  }

  private void insertLetterCounter(String aLanguage){
    aLanguage = aLanguage.toUpperCase();
    int[] theLetterCounter = myLetterCounter.get(aLanguage);

    ContentValues theValues = new ContentValues();
    for(char theChar = 'A'; theChar <='Z'; theChar++){
      theValues.put("LanguageCD", aLanguage);
      theValues.put("Letter", new String(new char[]{theChar}));
      theValues.put("Occurences", theLetterCounter[theChar - 'A']);
      Log.d(SQLiteStringDB.class.getName(), theLetterCounter[theChar - 'A'] + " occurences of " + theChar);
      getWritableDatabase().insert("Letters", null, theValues);
    }
  }
  
  private void insertLanguage(String aLanguage){
    aLanguage = aLanguage.toUpperCase();
    ContentValues theValues = new ContentValues();
    theValues.put("LanguageCD", aLanguage);
    getWritableDatabase().insert( "Language", null, theValues );
  }

  public boolean isLanguageLoaded(String aLanguage){
    aLanguage = aLanguage.toUpperCase();
    Cursor theCursor = null;
    try{
      theCursor = getReadableDatabase().query("Language", new String[]{"LanguageCD"}, "LanguageCD=?", new String[]{aLanguage}, null, null, null);
      return theCursor.getCount() != 0;
    }finally{
      if(theCursor != null){
        theCursor.close();
      }
    }
  }

  public RandomLetterHelper getRandomLetterHelper(String aLanguage){
    aLanguage = aLanguage.toUpperCase();
    Cursor theCursor = getReadableDatabase().rawQuery("Select Letter, Occurences from Letters where LanguageCD=? order by Letter", new String[]{aLanguage});
    Log.d(SQLiteStringDB.class.getName(), "Number of letter occurences '"  +theCursor.getCount() + "'");
    theCursor.moveToFirst();
    try{
      int[] theOccurences = new int[26];
      for(int i=0;i<theOccurences.length;i++){
        theOccurences[i] = theCursor.getInt(theCursor.getColumnIndex("Occurences"));
        theCursor.moveToNext();
      }
      return new RandomLetterHelper(theOccurences);
    }finally{
      theCursor.close();
    }
  }
  
  private List<String> getLanguageCache(String aLanguage, Map<String, List<String>> aCache){
    if(!aCache.containsKey(aLanguage)){
      aCache.put(aLanguage, new ArrayList<String>());
    }
    return aCache.get(aLanguage);
  }

  public boolean isValidWord(String aLanguage, String aWord){
    aLanguage = aLanguage.toUpperCase();
    aWord = aWord.toUpperCase();
    
    List<String> theLanguageCache = getLanguageCache(aLanguage, myWords);
    if(theLanguageCache.contains(aWord)) return true;
   
//    long t1 = System.currentTimeMillis();
//    Log.d(SQLiteStringDB.class.getName(), "Checking if '" + aWord + "' is valid");
    Cursor theCursor = getReadableDatabase().rawQuery("Select Word from Words where LanguageCD=? and Word = ?", new String[]{aLanguage, aWord});
//    boolean isHasRows = theCursor.getCount() > 0;
    boolean isHasRows = theCursor.moveToFirst();
    theCursor.close();
//    Log.d(SQLiteStringDB.class.getName(), aWord + "' is valid: '" + isHasRows + "' " + (System.currentTimeMillis() - t1) + " ms");
    if(isHasRows) theLanguageCache.add(aWord);
    return isHasRows;

    //    Cursor theCursor = getReadableDatabase().query("Words", new String[]{"Word"}, "LanguageCD=? and upper(Word)=?", new String[]{aLanguage, aWord.toUpperCase()}, null, null, null);
    //    Log.d(DatabaseHelper.class.getName(), "Checking if '" + aWord + "' is valid " + theCursor.getCount());
    //    return theCursor.getCount() > 0;
    
    
  }

  @Override
  public boolean isValidPrefix( String aLanguage, String aPrefix ) {
    aLanguage = aLanguage.toUpperCase();
    aPrefix = aPrefix.toUpperCase();
    
    List<String> theLanguageCache = getLanguageCache(aLanguage, myPrefixes);
    if(theLanguageCache.contains(aPrefix)) return true;

//    long t1 = System.currentTimeMillis();
//    Log.d(SQLiteStringDB.class.getName(), "Checking if '" + aPrefix + "' is valid");
    Cursor theCursor = getReadableDatabase().rawQuery("Select Word from Words where LanguageCD=? and Word like ?", new String[]{aLanguage, aPrefix.toUpperCase() + "%"});
//    boolean isHasRows = theCursor.getCount() > 0;
    boolean isHasRows = theCursor.moveToFirst();
    theCursor.close();
//    Log.d(SQLiteStringDB.class.getName(), aPrefix + "' is valid: '" + isHasRows + "' " + (System.currentTimeMillis() - t1) + " ms");
    if(isHasRows) theLanguageCache.add(aPrefix);
    return isHasRows;

  }


  @Override
  public void onUpgrade(SQLiteDatabase db, int aArg1, int aArg2) {
    db.execSQL("DROP TABLE IF EXISTS Language");
    db.execSQL("DROP TABLE IF EXISTS Words");
    db.execSQL("DROP TABLE IF EXISTS Letters");
    onCreate(db);
  }

  @Override
  public void beginInsertingWords(String aLanguage) {
    getWritableDatabase().beginTransaction();
  }

  @Override
  public void endInsertingWords(String aLangauge) {
    getWritableDatabase().endTransaction();
  }

  @Override
  public void insertingWordsCompleted( String aLanguage ) {
    insertLetterCounter( aLanguage );
    insertLanguage( aLanguage );
    getWritableDatabase().setTransactionSuccessful();
  }
}
